﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using HSPCS.Data;
namespace HSPCS.BLL
{
    public class DataBLL
    {
        Connector connector = new Connector();
        public DataTable GetColumn(string tableName)
        {
            string sql = "SELECT [Column Name] = c.name  " +
                        ", [Data type] = t.name " +
                        ", [Max Length] = c.max_length " +
                        ", c.is_nullable " +
                        ", [Primary Key] = ISNULL(i.is_primary_key, 0) " +
                        ", IsFK = ISNULL(fkc.parent_object_id / fkc.parent_object_id, 0) " +
                        "FROM sys.columns c  " +
                        "JOIN sys.types t  ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id " +
                        "LEFT JOIN ( " +
                        "SELECT i.[object_id], ic.column_id, i.is_primary_key " +
                        "FROM sys.indexes i  " +
                        "JOIN sys.index_columns ic  ON ic.[object_id] = i.[object_id] " +
                        "AND i.index_id = ic.index_id " +
                        "WHERE i.is_primary_key = 1 " +
                        ") i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id " +
                        "LEFT JOIN sys.foreign_key_columns fkc  ON fkc.parent_object_id = c.[object_id]  " +
                        "AND fkc.parent_column_id = c.column_id " +
                        "WHERE  c.object_id = OBJECT_ID('"+tableName+"') AND t.name != 'sysname'";
            DataTable dtTable = new DataTable();
            connector.ExecuteReader(ref dtTable, sql, null);
            return dtTable;
        }
        public DataTable GetTableList()
        {
            string sql = "SELECT TABLE_SCHEMA, TABLE_NAME  FROM INFORMATION_SCHEMA.TABLES "+
                            "WHERE TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = 'PCS'";
            DataTable dtTable = new DataTable();
            connector.ExecuteReader(ref dtTable, sql, null);
            return dtTable;
        }
        public bool AddNewTable( string sql)
        {
            if (connector.ExecuteNonQuery(sql, null) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public DataTable GetForeignKey(string tableName, string columnName)
        {
            string tableNameSub = tableName.Substring(4);
            string sql = "SELECT K_Table = FK.TABLE_NAME, " +
                            "FK_Column = CU.COLUMN_NAME, "+
                            "PK_Table = PK.TABLE_NAME " +
                            "FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C " +
                            "INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK " +
                            "ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME " +
                            "INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK " +
                            "ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME " +
                            "INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU " +
                            "ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME " +
                            "Where FK.TABLE_NAME = '" + tableNameSub + "' and CU.COLUMN_NAME = '" + columnName + "' ";
            DataTable dtTable = new DataTable();
            connector.ExecuteReader(ref dtTable, sql, null);
            if (dtTable.Rows.Count > 0)
            {

                string sqlSemi = "[PCS].[sp_Get2FirstCols]";
                SqlParameter[] sqlParam = new SqlParameter[1];
                sqlParam[0] = new SqlParameter("@tableName", dtTable.Rows[0][2].ToString());
                DataSet dataSet = connector.GetTablebyProcedure(sqlSemi, sqlParam);
                DataTable foreignData = new DataTable();
                return foreignData = dataSet.Tables[0];
            }
            else
            {
                return null;
            }
        }

        public DataTable GetFirstRecord(string tableName, List<string> listCol)
        {
            tableName = tableName.Insert(4, "[");
            tableName = tableName.Insert(tableName.Length,"]");
            string sql = "select top 1 ";
            for(int i=0;i<listCol.Count;i++)
            {
                if (i != listCol.Count - 1)
                {
                    sql += listCol[i] + ", ";
                }
                else
                {
                    sql += listCol[i];
                }
            }
            sql += " from "+ tableName + " order by "+ listCol[0];
            DataTable dtTable = new DataTable();
            connector.ExecuteReader(ref dtTable, sql, null);
            return dtTable;
        }

        public DataTable GetNextRecord(string tableName, List<string> listCol, string currentKey)
        {
            tableName = tableName.Insert(4, "[");
            tableName = tableName.Insert(tableName.Length, "]");
            string sql = "select top 1 ";
            for (int i = 0; i < listCol.Count; i++)
            {
                if (i != listCol.Count - 1)
                {
                    sql += listCol[i] + ", ";
                }
                else
                {
                    sql += listCol[i];
                }
            }
            sql += " from " + tableName + " where "+listCol[0]+" > "+ currentKey+ " order by " + listCol[0];
            DataTable dtTable = new DataTable();
            connector.ExecuteReader(ref dtTable, sql, null);
            return dtTable;
        }
        public DataTable GetPreviousRecord(string tableName, List<string> listCol, string currentKey)
        {
            tableName = tableName.Insert(4, "[");
            tableName = tableName.Insert(tableName.Length, "]");
            string sql = "select top 1 ";
            for (int i = 0; i < listCol.Count; i++)
            {
                if (i != listCol.Count - 1)
                {
                    sql += listCol[i] + ", ";
                }
                else
                {
                    sql += listCol[i];
                }
            }
            sql += " from " + tableName + " where " + listCol[0] + " < " + currentKey + " order by " + listCol[0] + " desc";
            DataTable dtTable = new DataTable();
            connector.ExecuteReader(ref dtTable, sql, null);
            return dtTable;
        }

        public DataTable GetRecordbyID(string tableName, List<string> listCol, string currentKey)
        {
            tableName = tableName.Insert(4, "[");
            tableName = tableName.Insert(tableName.Length, "]");
            string sql = "select top 1 ";
            for (int i = 0; i < listCol.Count; i++)
            {
                if (i != listCol.Count - 1)
                {
                    sql += listCol[i] + ", ";
                }
                else
                {
                    sql += listCol[i];
                }
            }
            sql += " from " + tableName + " where " + listCol[0] + " = " + currentKey + " order by " + listCol[0];
            DataTable dtTable = new DataTable();
            connector.ExecuteReader(ref dtTable, sql, null);
            return dtTable;
        }

        public string SubmitData(string tableName, List<Dictionary<string, string>> input)
        {
            tableName = tableName.Insert(4, "[");
            tableName = tableName.Insert(tableName.Length, "]");
            if (!CheckExist(tableName, input[0].FirstOrDefault().Key, input[0].FirstOrDefault().Value))
            {
                string sql = "insert into " + tableName + " ( ";
                foreach (Dictionary<string, string> child in input)
                {
                    sql += child.FirstOrDefault().Key + ",";
                }
                sql = sql.Substring(0, sql.Length - 1);
                sql += " ) output inserted."+input[0].FirstOrDefault().Key+ " values( ";
                foreach (Dictionary<string, string> child in input)
                {
                    sql += "'"+ child.FirstOrDefault().Value + "',";
                }
                sql = sql.Trim().Substring(0, sql.Length - 1);
                sql += " )";
                object objResult = new object();
                connector.ExecuteScalar(ref objResult, sql, null);
                return (objResult == null) ? String.Empty : objResult.ToString();
            }
            else
            {
                string sql = "update " + tableName + " set ";
                for(int i = 1;i< input.Count;i++)
                {
                     sql += input[i].FirstOrDefault().Key + " = '" + input[i].FirstOrDefault().Value + "',";
                }
               
                sql = sql.Trim().Substring(0, sql.Length - 1);
                sql += "output inserted." + input[0].FirstOrDefault().Key;
                sql += " where " + input[0].FirstOrDefault().Key + " = " + input[0].FirstOrDefault().Value;

                Object objResult = new Object();
                connector.ExecuteScalar(ref objResult, sql, null);
                if(objResult!=null)
                {
                    string strResult;
                    strResult = objResult.ToString();
                    return strResult;
                }
                return null;
            }
        }
        public bool CheckExist(string tableName, string keyID,string keyData)
        {
            string sql = "select top 1 " + keyID + " from " + tableName + " where " + keyID + " = " + keyData;
            DataTable dtTable = new DataTable();
            connector.ExecuteReader(ref dtTable, sql, null);
            if (dtTable.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public string Test()
        {
            Random ran = new Random();
            string sql = "insert into PCS.[System] (SystemID,Name) output inserted.SystemID values ('" + ran.Next(5, 100) + "','test insert')";
            Object objResult = new Object();
            connector.ExecuteScalar(ref objResult, sql, null);
            if (objResult == System.DBNull.Value)
            {
                return String.Empty;
            }
            else
            {
                return objResult.ToString();
            }
        }

        public DataTable Delete(string tableName, List<Dictionary<string, string>> input)
        {
            tableName = tableName.Insert(4, "[");
            tableName = tableName.Insert(tableName.Length, "]");
            string sqlSelect = "select top 1 ";
            foreach (Dictionary<string, string> child in input)
            {
                sqlSelect += child.FirstOrDefault().Key + ",";
            }
            sqlSelect = sqlSelect.Trim().Substring(0, sqlSelect.Length - 1);
            sqlSelect += " from " + tableName + " where " + input[0].FirstOrDefault().Key + " > " + input[0].FirstOrDefault().Value + " order by " + input[0].FirstOrDefault().Key;
            DataTable dtTable = new DataTable();
            connector.ExecuteReader(ref dtTable, sqlSelect, null);
            string sqlDelete = "delete " + tableName + " output deleted." + input[0].FirstOrDefault().Key + " where " + input[0].FirstOrDefault().Key + " = " + input[0].FirstOrDefault().Value;
            Object objResult = new Object();
            connector.ExecuteScalar(ref objResult, sqlDelete, null);
            if (objResult != System.DBNull.Value && input[0].FirstOrDefault().Value == objResult.ToString())
            {
                return dtTable;
            }
            else
            {
                return null;
            }
            

        }
    }
}